package com;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import java.util.StringTokenizer;

public class CreateShopConfig {
	public static void dropTable() throws ClassNotFoundException, SQLException {
		Connection connection = null;
		try {
			Class.forName("org.postgresql.Driver");
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/openerpdb", "openpg",
					"openpgpwd");
			String sql = "drop table shopcsv";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.execute();
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			connection.close();
		}
	}

	public static void createTable() throws Exception {

		Connection connection = null;
		try {
			Class.forName("org.postgresql.Driver");
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/openerpdb", "openpg",
					"openpgpwd");
			String sql = "create table shopcsv (id serial, shopname varchar(40),"
					+ "csvheader varchar(100),"
					+ " openerpheader varchar(100),"
					+ "importexport varchar(10), "
					+ "primary key (id),"
					+ "unique (shopname,csvheader,openerpheader));";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.execute();
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			connection.close();
		}
	}

	public static void insertIntoTable(ArrayList<ConfigShopBean> shopBeans)
			throws Exception {
		Connection connection = null;
		try {
			Class.forName("org.postgresql.Driver");
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/openerpdb", "openpg",
					"openpgpwd");
			String sql = "insert into  shopcsv (shopname, csvheader,openerpheader,importexport)"
					+ "values (?,?,?,?);";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			for (ConfigShopBean shopBean : shopBeans) {
				pstmt.setString(1, shopBean.getShopName());
				pstmt.setString(2, shopBean.getCsvHeader());
				pstmt.setString(3, shopBean.getOpenerpHeader());
				pstmt.setString(4, shopBean.getImportExport());
				pstmt.addBatch();
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connection.close();
		}

	}

	public static ArrayList<ConfigShopBean> parseData() throws IOException {

		Properties prop = new Properties();
		FileInputStream in = new FileInputStream(CreateShopConfig.class
				.getClassLoader().getResource("csvimport.properties").getFile());
		prop.load(in);
		String line = (String) prop.get("shop1");
		StringTokenizer st1 = new StringTokenizer(line, ";", false);

		ArrayList<ConfigShopBean> configShops = new ArrayList<ConfigShopBean>();
		while (st1.hasMoreTokens()) {
			String entry1 = st1.nextToken();
			StringTokenizer st2 = new StringTokenizer(entry1, ":", false);
			int i = 0;
			ConfigShopBean c = new ConfigShopBean();
			c.setShopName("shop1");
			configShops.add(c);
			while (st2.hasMoreTokens()) {
				String entry2 = st2.nextToken();
				System.out.println(entry2);
				i++;
				switch (i) {
				case 1:
					c.setOpenerpHeader(entry2);
				case 2:
					c.setCsvHeader(entry2);
				case 3:
					c.setImportExport(entry2);
				}
			}
		}

		return configShops;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {

			dropTable();

			createTable();
			
			insertIntoTable(parseData());

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
